package cn.com.dhcc.turbo.dao.stat;

import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import cn.com.dhcc.app.core.CM; 
import cn.com.dhcc.app.core.dao.BaseDao;
import cn.com.dhcc.app.core.exception.DaoException; 
import cn.com.dhcc.app.core.service.Log;
import cn.com.dhcc.app.core.service.Log.LOGTYPE; 
import cn.com.dhcc.turbo.entity.stat.ExchStatManage;
import cn.com.dhcc.turbo.entity.stat.wapper.ExchStatManageRowWapper; 

@Repository
public class ExchStatManageDao extends BaseDao {
	@SuppressWarnings("deprecation")
	private static Logger logger = Log.getExistLogger(LOGTYPE.TURBO);
	/**
	 * 节点/系统注册频率（年） 
	 * @param nt 统计时间.
	 * @return
	 * @throws DaoException
	 */ 
	public List<ExchStatManage> getStBtReg(String nt) throws DaoException {
		String msg = ""; 
		String sql = "SELECT   num , TO_CHAR (nt, 'yyyy-mm') AS nt , REG_TYPE  "
				+" FROM "+SI_TABLE("TURBO_ST_BT_REG_Y")+" WHERE TO_CHAR (nt, 'yyyy') = ?    ORDER  BY  nt  asc ";
		try {
			return CM.getDao().list("获取数据统计", sql, new ExchStatManageRowWapper(), new Object[] {nt});
		} catch (DaoException e) {
			msg = "获取数据统计失败";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
	}
	/**
	 * 节点/系统注册频率（年） 
	 * @param nt 统计时间.
	 * @return nt.
	 * @throws DaoException
	 */ 
	public List<ExchStatManage> getStBtRegTime(String nt) throws DaoException {
		String msg = ""; 
		String sql = "SELECT  DISTINCT   TO_CHAR (nt, 'yyyy-mm') AS nt    "
				+" FROM "+SI_TABLE("TURBO_ST_BT_REG_Y")+" WHERE TO_CHAR (nt, 'yyyy') = ?    ORDER  BY  nt  asc ";
		try {
			return CM.getDao().list("获取Time", sql, new ExchStatManageRowWapper(), new Object[] {nt});
		} catch (DaoException e) {
			msg = "获取time统计失败";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
	}
	
	/**
	 * 节点间交换频率(月/年)
	 * @param nt 统计时间
	 * @param stat_level 统计级别： 1 节点3 系统
	 * @param timeType
	 * @return
	 * @throws DaoException
	 */ 
    public List<ExchStatManage> getStBtNodeNum(String timeType,String stat_level,String nt) throws DaoException{
    	String msg="";
		String tableType = "";
		String ntModel = "";
		String ytimeModel = "";
    	try {
    		if(timeType.equals("month")){
    			tableType = "M";
    			ntModel = "yyyy-mm-dd";
    			ytimeModel="yyyy-mm";
    		}else{
    			tableType = "Y";
    			ntModel = "yyyy-mm";
    			ytimeModel="yyyy";
    		}
    		
    			String sql=" select TO_CHAR (a.nt, '"+ntModel+"') AS nt,a.from_id, a.to_id, a.FROM_NUM,a.to_num  as send_num  "
    				      +" from "+SI_TABLE("TURBO_ST_BT_NODE_NUM_"+tableType+"")+"  a "
    				      +" where a.STAT_LEVEL=? and TO_CHAR (a.nt, '"+ytimeModel+"') = ?";
    			 return CM.getDao().list("获取数据统计", sql, new ExchStatManageRowWapper(), new Object[] {stat_level,nt});
    			
    		
		} catch (DaoException e) {
			msg = "请选择系统信息";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
    	
    }
    /**
     * 获取节点信息
     * */
    public List<ExchStatManage> getStBtNodeNumName(String timeType) throws DaoException{
    	String msg="";
    	String tableType = ""; 
    	if(timeType.equals("month")){
			tableType = "M"; 
		}else{
			tableType = "Y"; 
		}
    	String sql="select DISTINCT a.FROM_ID,b.NODE_NAME from_name,c.NODE_NAME to_name " 
    	 +" from " +SI_TABLE("TURBO_ST_BT_NODE_NUM_"+tableType+"")+"  a, "+SI_TABLE(" TURBO_NODE_INFO ")+"  b ,"+SI_TABLE(" TURBO_NODE_INFO ")+" c where b.id=a.FROM_ID" +
    	 		" and a.to_id=c.id";
    	try {
			return CM.getDao().list("获取Name信息", sql, new ExchStatManageRowWapper(), new Object[] {});
		} catch (DaoException e) {
			msg = "获取Name信息失败";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
    }
    /**
     * 获取系统信息
     * */
    public List<ExchStatManage> getStBtNodeNumSysName(String timeType) throws DaoException{
    	String msg="";
    	String tableType = ""; 
    	if(timeType.equals("month")){
			tableType = "M"; 
		}else{
			tableType = "Y"; 
		}
    	String sql="select distinct a.from_id,b.adapter_name from_name,c.adapter_name to_name " 
    	 +" from " +SI_TABLE("TURBO_ST_BT_NODE_NUM_"+tableType+"")+"  a, "+SI_TABLE("turbo_adapter_info")+"  b, "+SI_TABLE("turbo_adapter_info")+" c " +
    	 		"  where b.id=a.FROM_ID and c.id=a.to_id";
    	try {
			return CM.getDao().list("获取Name信息", sql, new ExchStatManageRowWapper(), new Object[] {});
		} catch (DaoException e) {
			msg = "获取Name信息失败";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
    }
    
    /**
	 * 内外网交换频率最高的业务数据（月/年）
	 * @param nt 统计时间
	 * @param stat_level 统计级别： 1 节点 2 业务域 3 系统
	 * @param timeType
	 * @return
	 * @throws DaoException
	 */ 
    public List<ExchStatManage> getStBtNetTop(String timeType,String stat_level,String nt ) throws DaoException{
    	String msg="";
		String tableType = "";
		String ntModel = "";
		String ytimeModel = ""; 
    	try {
    		if(timeType.equals("month")){
    			tableType = "M";
    			ntModel = "yyyy-mm-dd";
    			ytimeModel="yyyy-mm";
    		}else{
    			tableType = "Y";
    			ntModel = "yyyy-mm";
    			ytimeModel="yyyy";
    		}
    		if(stat_level.equals("1")){
    			String sql=" select TO_CHAR (a.nt, '"+ntModel+"') AS nt,a.from_id, a.to_id, a.FROM_NUM ," +
    					" case when b.node_name is null then a.from_id else b.node_name   end from_name, " +
    					"  case when c.node_name is null then a.to_id else c.node_name end to_name,  " +
    					"   a.FROM_NUM,a.to_num send_num "
    			      +" from "+SI_TABLE("TURBO_ST_BT_NET_TOP_"+tableType+"")+"  a " +
    			      		"  left join "+SI_TABLE("turbo_node_info")+" b on b.id=a.from_id " +
    			      		"  left join "+SI_TABLE("turbo_node_info")+" c on c.id=a.to_id  " 
    			      +" where a.STAT_LEVEL=?   and TO_CHAR (a.nt, '"+ytimeModel+"') = ?";
    						return CM.getDao().list("获取数据统计", sql, new ExchStatManageRowWapper(), new Object[] {stat_level,nt}); 
    		 }else {
    			 String sql=" select TO_CHAR (a.nt, '"+ntModel+"') AS nt,a.from_id, a.to_id, a.from_num ," +
     					" case when b.adapter_name is null then a.from_id else b.adapter_name end from_name, " +
     					"  case when c.adapter_name is null then a.to_id else c.adapter_name  end to_name,  " +
     					"   a.from_num,a.to_num send_num "
     			      +" from "+SI_TABLE("TURBO_ST_BT_NET_TOP_"+tableType+"")+"  a " +
     			      		"  left join "+SI_TABLE("turbo_adapter_info")+" b on b.id=a.from_id " +
     			      		"  left join "+SI_TABLE("turbo_adapter_info")+" c on c.id=a.to_id  " 
     			      +" where a.STAT_LEVEL=?   and TO_CHAR (a.nt, '"+ytimeModel+"') = ?";
       						return CM.getDao().list("获取数据统计", sql, new ExchStatManageRowWapper(), new Object[] {stat_level, nt}); 
    		 }
    		
		} catch (DaoException e) {
			msg = "获取数据统计失败";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
    	
    }
     
    
	public List<ExchStatManage> getStBtNetTopName(String timeType) throws DaoException{
    	String msg=""; 
    	String tableType = ""; 
    	if(timeType.equals("month")){
			tableType = "M"; 
		}else{
			tableType = "Y"; 
		}
    	String sql="select DISTINCT a.FROM_ID,( case when b.node_name is null then a.from_id else b.node_name end ) NODE_NAME " 
    	 +" from " +SI_TABLE("TURBO_ST_BT_NET_TOP_"+tableType+"")+"  a  left join "+SI_TABLE(" TURBO_NODE_INFO ")+"  b on b.id=a.FROM_ID";
    	try {
			return CM.getDao().list("获取Name信息", sql, new ExchStatManageRowWapper(), new Object[] {});
		} catch (DaoException e) {
			msg = "获取Name信息失败";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
    }
    
	
	/**
     * 获取系统信息
     * */
    public List<ExchStatManage> getStBtNetTopSysName(String timeType) throws DaoException{
    	String msg="";
    	String tableType = ""; 
    	if(timeType.equals("month")){
			tableType = "M"; 
		}else{
			tableType = "Y"; 
		}
    	String sql="select DISTINCT a.FROM_ID,( case when b.sys_name is null then a.from_id else b.sys_name end ) sys_name " 
    	 +" from " +SI_TABLE("TURBO_ST_BT_NET_TOP_"+tableType+"")+"  a left join "+SI_TABLE(" TURBO_sys_INFO ")+"  b on b.id=a.FROM_ID";
    	try {
			return CM.getDao().list("获取Name信息", sql, new ExchStatManageRowWapper(), new Object[] {});
		} catch (DaoException e) {
			msg = "获取Name信息失败";
			logger.error(msg, e);
			throw new DaoException(msg, e);
		}
    }
    
    
	
	    /**
		 * 交换频率最高的业务数据（月/年）
		 * @param nt 统计时间
		 * @param FROM_NODE_ID  from节点id ,TO_NODE_ID to节点id
		 * @param stat_level 统计级别：1 内外网 2 管理范围内 3 节点之间
		 * @param DATA_TYPE_ID 系统类型id
		 * @param timeType
		 * @return
		 * @throws DaoException
		 */ 
	    public List<ExchStatManage> getStBtDtTop(String timeType,String from_node_id,String to_node_id,String stat_level,String nt) throws DaoException{
	    	
	    	String msg="";
	    	String tableType = ""; 
			String ytimeModel = ""; 
	    	try {
			 
			if(timeType.equals("month")){
				tableType = "M"; 
				ytimeModel="yyyy-mm";
			}else{
				tableType = "Y"; 
				ytimeModel="yyyy";
			}
			if(stat_level.equals("3")){ 
				String sql=" SELECT  case when C.data_name is null then a.data_code else C.data_name end node_name, a.num, B1.node_name from_name,B2.node_name to_name "
		                 +" FROM "+SI_TABLE(" TURBO_ST_BT_DT_TOP_"+tableType+"")+"  A" +
		                 		" left join  "+SI_TABLE(" TURBO_NODE_INFO ")+"  B1 on B1.id= from_node_id " +
		                 		" left join  "+SI_TABLE(" TURBO_NODE_INFO ")+"  B2 on B2.id= to_node_id " +
		                 		" left join  "+SI_TABLE(" TURBO_DATA_CATEGORY ")+"  C on C.data_code= a.data_code " 
		                 +" WHERE from_node_id=? and to_node_id=? AND TO_CHAR (a.nt, '"+ytimeModel+"') = ?  AND a.STAT_LEVEL = ? ";
			  
				 return CM.getDao().list("获取数据统计", sql, new ExchStatManageRowWapper(), new Object[] {from_node_id,to_node_id,nt,stat_level});
				
			}else if(stat_level.equals("1")){
				String sql=" SELECT A .num,  case when b.data_name is null then a.data_code else b.data_name end node_name "
                      +" FROM "+SI_TABLE("TURBO_ST_BT_DT_TOP_"+tableType+"")+" A" +
                      		" left join "+SI_TABLE("TURBO_DATA_CATEGORY")+" b on  a.data_code=b.data_code"
                     +" WHERE  TO_CHAR (A .nt, '"+ytimeModel+"') =?  AND A .STAT_LEVEL =?";
				 return CM.getDao().list("获取数据统计", sql, new ExchStatManageRowWapper(), new Object[] {nt,stat_level});
			}else {
				String sql="SELECT  A .num,b.data_name AS node_name  FROM "+SI_TABLE("turbo_st_bt_dt_top_"+tableType+"") +" a, "+SI_TABLE("turbo_data_category") +" b "
						+" WHERE a.data_code=b.data_code  AND TO_CHAR (A .nt, '"+ytimeModel+"') =? AND A .STAT_LEVEL =? ";
				 return CM.getDao().list("获取数据统计", sql, new ExchStatManageRowWapper(), new Object[] {nt,stat_level});
				
			}
			
			} catch (DaoException e) {
				msg = "获取数据统计失败";
				logger.error(msg, e);
				throw new DaoException(msg, e);
			}
	    	
	    }
	    
	    /**
	     *获取系统对应节点信息
	     *
	     */
	    public List<ExchStatManage> getSysInfos() throws  DaoException{
	    	String msg=""; 
	    	try {
	    		String sql="SELECT SYS_NAME ,id  from "+SI_TABLE(" TURBO_SYS_INFO ");
	    		return CM.getDao().list("获取节点信息", sql,  new ExchStatManageRowWapper(), new Object[] {});
			} catch (Exception e) {
				msg = "获取节点信息失败";
				logger.error(msg, e);
				throw new DaoException(msg, e);
			}
	    	
	    }
	     
	    /**
	     *获取系统对应节点信息
	     *
	     */
	    public List<ExchStatManage> getNodeInfos() throws  DaoException{
	    	String msg=""; 
	    	try {
	    		String sql="SELECT node_NAME ,id  from "+SI_TABLE(" TURBO_NODE_INFO ");
	    		return CM.getDao().list("获取节点信息", sql,  new ExchStatManageRowWapper(), new Object[] {});
			} catch (Exception e) {
				msg = "获取节点信息失败";
				logger.error(msg, e);
				throw new DaoException(msg, e);
			}
	    	
	    }
    
}
